In [1]:
import pandas as pd
import numpy as np
from langdetect import detect as langdetect
import random
seed = 42
np.random.seed(seed)
random.seed(seed)
import sys
sys.path.append("..")
from data.labeled.raw import CWA as cwa, CWR as cwr
from data.labeled.preprocessed import LABELED_CITIES_PATH, RISKS_MAPPING_PATH, SEVERITY_MAPPING_PATH, IMPUTATION_REPORT_PATH
Preprocess whole dataset¶
In [2]:
cwa.head()
Out[2]:
In [3]:
cwr.head()
Out[3]:
Normalize columns names¶
In [4]:
# Convert columns names to lowercase
cwa.columns = cwa.columns.str.lower().str.replace(" ", "_").str.replace("'","")
cwr.columns = cwr.columns.str.lower().str.replace(" ", "_").str.replace("'","")
Find counts of nulls per column¶
In [5]:
cwr.isnull().sum()
Out[5]:
In [6]:
cwa.isnull().sum()
Out[6]:
Find columns that exist in both datasets¶
In [7]:
id_columns = list(set(cwr.columns) & set(cwa.columns))
id_columns
Out[7]:
Check how many nulls exist in these columns¶
In [8]:
cwr[id_columns].isnull().sum()
Out[8]:
In [9]:
cwa[id_columns].isnull().sum()
Out[9]:
Replace nulls there with "nan" strings, so that merging is done correctly¶
In [10]:
cwr['c40'] = cwr['c40'] == 'C40'
cwr[id_columns] = cwr[id_columns].fillna('nan')
In [11]:
cwa['c40'] = cwa['c40'] == 'C40'
cwa[id_columns] = cwa[id_columns].fillna('nan')
Check id_columns uniqueness¶
In [12]:
len(cwa[id_columns]), len(cwa[id_columns].drop_duplicates())
Out[12]:
In [13]:
len(cwr[id_columns]), len(cwr[id_columns].drop_duplicates())
Out[13]:
Aggregate by grouping by id_columns and applying set operations to other colums¶
In [14]:
cwa_agg = cwa.groupby(id_columns).agg(set).reset_index()
cwr_agg = cwr.groupby(id_columns).agg(set).reset_index()
In [15]:
len(cwa_agg[id_columns]), len(cwa_agg[id_columns].drop_duplicates())
Out[15]:
In [16]:
len(cwr_agg[id_columns]), len(cwr_agg[id_columns].drop_duplicates())
Out[16]:
Make sure that risks magnitude (which is going to be used as an entry label) has a single value¶
In [17]:
cwr_agg.magnitude = cwr_agg.magnitude.apply(lambda x: 'Extremely serious' if 'Extremely serious' in x else ('Serious' if 'Serious' in x else list(x)[0]))
In [18]:
cwr_agg.magnitude.unique()
Out[18]:
Merge (outer) datasets and find the columns that are set (list_columns)¶
filling the non-merged rows with {np.nan} on list_columns to make sure there is contingency in each column
In [19]:
cw_data = pd.merge(cwa_agg, cwr_agg, on=id_columns, how='outer')
list_columns = cw_data.iloc[0].apply(lambda x: isinstance(x, set))
list_columns = list_columns[list_columns].index
cw_data[list_columns] = cw_data[list_columns].applymap(lambda x: {np.nan} if pd.isna(x) else x)
In [20]:
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[20]:
In [21]:
cw_data.head()
Out[21]:
Extract longitude and latitude from city_location¶
In [22]:
cw_data['city_location'] = cw_data['city_location'].apply(eval)
cw_data['latitude'] = cw_data['city_location'].apply(lambda x: x[0])
cw_data['longitude'] = cw_data['city_location'].apply(lambda x: x[1])
id_columns = [x for x in id_columns] + ['latitude','longitude']
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[22]:
Find list columns that are singular (every element contains a length 1 list)¶
In [23]:
cw_value_columns = [ x for x in cw_data.columns if x not in id_columns]
# Values that can be converted back to strings from sets
singular_cols = cw_data.set_index(id_columns)[list_columns].applymap(lambda x: len(x)==1).product() > 0
singular_cols
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[23]:
Convert singular columns to non list ones¶
In [24]:
# magnitude is nicely singular per id
cw_data.loc[:, singular_cols[singular_cols].index] = cw_data.loc[:, singular_cols[singular_cols].index].applymap(lambda x: list(x)[0])
list_columns = [x for x in list_columns if x not in singular_cols[singular_cols].index]
Remove entries that are invalid or in different language than English from the list columns¶
also convert sets to lists
In [25]:
def try_lang_detect_except(x):
try:
return (langdetect(x)=='en')
except:
return False
cw_data = cw_data.applymap(lambda x: [t for t in x if not pd.isnull(t) and (t!='') and (t!='ERROR: #NAME?') and try_lang_detect_except(t)] if isinstance(x,set) else x)
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[25]:
Fix specific risks annotations by inspection¶
In [26]:
cw_data.risks_to_citys_water_supply.unique()
Out[26]:
In [27]:
cw_data.risks_to_citys_water_supply = cw_data.risks_to_citys_water_supply.apply(lambda x: x.replace('Inadequate or ageing infrastructure','Inadequate or aging infrastructure'))
cw_data['risks_to_citys_water_supply'] = cw_data['risks_to_citys_water_supply'].apply(lambda x: x.replace('Declining water quality: Declining water quality', 'Declining water quality'))
cw_data.risks_to_citys_water_supply.unique()
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[27]:
In [28]:
cw_data.drop_duplicates(subset=id_columns,inplace=True)
In [29]:
cw_data.head()
Out[29]:
In [30]:
to_drop = ['timescale','current_population','population_year']
cw_data['population_year'] = cw_data['population_year'].fillna(0)
t = cw_data.sort_values('population_year' ).groupby([x for x in cw_data.columns if (x !='population_year') and x not in list_columns],dropna=False).last().reset_index()
In [31]:
cw_data
Out[31]:
Drop metadata columns that will not be used¶
In [32]:
cw_data.drop(columns=['timescale','population_year'],inplace=True)
cw_data.rename(columns={'current_population':'population'},inplace=True)
In [33]:
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[33]:
Generate for each of the list columns a new one that contains the number of elements¶
Create value_columns that contains the names of all the list columns + the created ones
In [34]:
value_columns = set([x for x in cw_value_columns if x not in to_drop] + ['risks_to_citys_water_supply'])
list_columns = cw_data.iloc[0].apply(lambda x: isinstance(x, list))
list_columns = list_columns[list_columns].index
len_columns = set()
for x in value_columns.copy():
if x in list_columns:
cw_data[x+'_n'] = cw_data[x].apply(lambda x: len(x) if x else 1)
len_columns.add(x+'_n')
value_columns = value_columns.union(len_columns)
value_columns
Out[34]:
In [35]:
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[35]:
Filter by C40¶
We are making the assumption that, given that those cities participate in a specific program, they will also have better structured data. So this subset will be used as a reference for the imputation
In [36]:
c40_data = cw_data[cw_data['c40']].copy()
c40_data.shape
Out[36]:
Find the null percentage of list columns¶
In [37]:
list_nulls = c40_data[list_columns].applymap(lambda x: len(x) == 0)
list_nulls.mean()
Out[37]:
In [38]:
c40_data['magnitude'].unique()
Out[38]:
Create a report of the values of magnitude¶
In [39]:
c40_risks = c40_data[id_columns+['magnitude']].copy()
c40_risks['low'] = c40_risks['magnitude'] == 'Less Serious'
c40_risks['medium'] = c40_risks['magnitude'] == 'Serious'
c40_risks['high'] = c40_risks['magnitude'] == 'Extremely serious'
c40_risks['unknown'] = c40_risks['magnitude'].isna()
c40_risks = c40_risks.drop(columns=['magnitude']).groupby([x for x in id_columns if x!="risks_to_citys_water_supply"]).agg(sum).reset_index()
c40_risks
Out[39]:
In [40]:
from sklearn.preprocessing import LabelEncoder
In [41]:
from utils.nlp import SimilarityAnalysis, create_sim_vector
In [42]:
c40_data.risks_to_citys_water_supply = c40_data.risks_to_citys_water_supply.apply(lambda x: np.nan if x=='nan' else x)
In [43]:
c40_data[c40_data.risks_to_citys_water_supply.isnull()]
Out[43]:
In [44]:
c40_data.risks_to_citys_water_supply.unique()
Out[44]:
Fit Similarity Analysis¶
per string list column of the value columns, by concatenating strings together. Vectorize all the string list columns based on their in between similarities
In [45]:
analyses = {x:SimilarityAnalysis() for x in value_columns if x in list_columns}
In [46]:
vectorized = np.hstack([analyses[x].fit_transform(c40_data[x]) for x in analyses])
Encode magnitude and risks into numerical labels¶
In [47]:
cols_to_encode = [x for x in value_columns if x not in list_columns and x not in len_columns]
c40_data[cols_to_encode] = c40_data[cols_to_encode].fillna('nan')
encoders = {x:LabelEncoder() for x in cols_to_encode}
In [48]:
encoded = np.array([encoders[x].fit_transform(c40_data[x]) for x in cols_to_encode]).T.astype(float)
for t in range(len(cols_to_encode)):
encoded[encoded[:,t] == np.where(encoders[cols_to_encode[t]].classes_=='nan')[0],t] = np.nan
In [49]:
lab_freqs = c40_data[cols_to_encode].groupby(cols_to_encode,as_index=False).size()
lab_freqs
Out[49]:
Create dataset that contains the encoded labels, the vectorized columns and the columns referring the lists lengths¶
In [50]:
whole = np.hstack([encoded, vectorized, c40_data[len_columns]])
In [51]:
import sys
sys.path.append('..')
from labeled_preprocessing.imputation import LabeledDatasetImputer
Fit LabeledDatasetImputer and transform dataset¶
(LabeledDatasetImputer is a union of two Iterative Imputers , one for labels and one for features)
In [52]:
imputer = LabeledDatasetImputer(verbose=2,k_features_per_label=0, seed=seed)
continuous_imputed, y = imputer.fit_transform(whole[:,2:], whole[:,:2], ret_imputed_x=True)
Use the prediction power on the imputed data as measurement of coherent imputation.¶
In [53]:
from sklearn.svm import SVC
from sklearn.linear_model import LogisticRegression
from sklearn.preprocessing import StandardScaler
from sklearn.model_selection import cross_val_score
from sklearn.preprocessing import StandardScaler
from sklearn.pipeline import make_pipeline
simple_model = make_pipeline(StandardScaler(), SVC(kernel='linear', C=0.01, random_state=seed))
scoring = 'accuracy'
scores = cross_val_score(simple_model, continuous_imputed, y[:,cols_to_encode.index('magnitude')], cv=5,scoring=scoring)
np.mean(scores)
Out[53]:
Plot features correlation¶
In [54]:
import matplotlib.pyplot as plt
flg = np.all(~np.isnan(whole),axis=1), np.hstack([[True, True], imputer.selection_mask[:]])
corr = np.corrcoef(whole[flg[0],:][:,flg[1]].T)
print(corr.shape)
plt.matshow(corr)
Out[54]:
Retrieve the categorical imputed labels¶
In [55]:
imputed = np.array([
encoders[x].inverse_transform(
y[:, c].astype(int)
) for c,x in enumerate(cols_to_encode)]).T
In [56]:
c40_data_imputed = c40_data.copy()
In [57]:
c40_data_imputed[cols_to_encode] = imputed
In [58]:
c40_data_imputed.head()
Out[58]:
Find which labels have been updated after imputation¶
In [59]:
imp_lab_freqs = c40_data_imputed[cols_to_encode].groupby(cols_to_encode,as_index=False).size()
imp_ret = pd.merge(lab_freqs, imp_lab_freqs,suffixes=('','_imp'), how='right',on=cols_to_encode)
imp_ret['increase'] = (imp_ret['size_imp'] - imp_ret['size'])/imp_ret['size']
imp_ret
Out[59]:
Make sure that all risks have been encoded and are imputation outcome candidates¶
In [60]:
set([x for x in cw_data['risks_to_citys_water_supply'] if x not in encoders['risks_to_citys_water_supply'].classes_])
Out[60]:
Impute whole dataset¶
In [61]:
cw_data['risks_to_citys_water_supply'].value_counts()
Out[61]:
Apply encoding and vectorization¶
based on fitted encoders and SimilarityAnalysis objects
In [62]:
cw_data[cols_to_encode] = cw_data[cols_to_encode].fillna('nan')
encoded = np.array([encoders[x].transform(cw_data[x]) for x in cols_to_encode]).T.astype(float)
for t in range(len(cols_to_encode)):
encoded[encoded[:,t] == np.where(encoders[cols_to_encode[t]].classes_=='nan')[0],t] = np.nan
In [63]:
all_vectorized = np.hstack([analyses[x].transform(cw_data[x]) for x in value_columns if x in list_columns])
Apply imputation¶
using the trained imputer
In [64]:
all_imputed_x, all_imputed_y = imputer.transform(np.hstack([all_vectorized,cw_data[len_columns]]), encoded, ret_imputed_x=True)
In [65]:
all_imputed_y_dec = np.array([
encoders[x].inverse_transform(
all_imputed_y[:, c].astype(int)
) for c,x in enumerate(cols_to_encode)]).T
In [66]:
len(cw_data[id_columns]), len(cw_data[id_columns].drop_duplicates())
Out[66]:
In [67]:
cw_data_imputed = cw_data.copy()
cw_data_imputed[cols_to_encode] = all_imputed_y_dec
cw_data_imputed.drop_duplicates(id_columns,inplace=True)
Plot features correlation¶
In [68]:
import matplotlib.pyplot as plt
corr = np.corrcoef(np.hstack([all_imputed_y, all_imputed_x]).T)
print(corr.shape)
plt.matshow(corr)
Out[68]:
In [69]:
scores = cross_val_score(simple_model, all_imputed_x, all_imputed_y[:,cols_to_encode.index('magnitude')], cv=5,scoring=scoring)
np.mean(scores)
Out[69]:
The score did not have significant drop, so we can assume that the imputation was cohesive across data
Create output dataset¶
In [205]:
final_labeled_data = cw_data_imputed[['city','latitude','longitude','country','population','c40','magnitude','risks_to_citys_water_supply']].copy()
final_labeled_data.rename(columns={'magnitude': 'risk','risks_to_citys_water_supply': 'description'},inplace=True)
In [206]:
final_labeled_data[['city','latitude','longitude','c40', 'population','description','risk']]
Out[206]:
In [207]:
cw_data_imputed[id_columns].drop_duplicates().shape
Out[207]:
Pivoting risks description, so that each row is unique per city¶
In [208]:
risks = final_labeled_data.description.unique()
risks_description = {risk: f'risk{c}' for c,risk in enumerate(risks)}
risks_df = pd.Series(risks_description).to_frame()
risks_df.reset_index(inplace=True)
risks_df.columns=['description','code']
final_labeled_data['description'] = final_labeled_data['description'].apply(lambda x: risks_description[x])
risks_df
Out[208]:
In [209]:
main_index=['city','latitude','longitude', 'country']
Check if there are cities that are listed to be both in and not in C40¶
and drop the ones that are not
In [210]:
c40_check = final_labeled_data.groupby(main_index)['c40'].nunique()
c40_check[c40_check>1]
Out[210]:
In [211]:
final_labeled_data.city.nunique()
Out[211]:
In [212]:
final_labeled_data[final_labeled_data.city == 'Santiago']
Out[212]:
In [221]:
final_labeled_data = final_labeled_data.sort_values(by='c40').groupby(main_index + ['description']).last().reset_index()
final_labeled_data = final_labeled_data.drop(columns=['c40']).merge(final_labeled_data.groupby(main_index)['c40'].max().reset_index(),on=main_index)
final_labeled_data[final_labeled_data.city=='Santiago']
Out[221]:
In [222]:
final_labeled_data = final_labeled_data.sort_values(by='c40').groupby(main_index + ['description']).last().reset_index()
Fix population¶
In [223]:
pop_df = final_labeled_data.groupby(index)[['population']
].max().reset_index().drop_duplicates()
final_labeled_data = final_labeled_data.drop(columns=['population']).merge(pop_df.reset_index(), on=index)
In [224]:
final_labeled_data = final_labeled_data.pivot(index= index + ['c40','population'], columns='description', values='risk').reset_index()
Encode risks from 0 to 1¶
In [225]:
severity_mapping = {'Less Serious':1, "Serious":2, 'Extremely serious':3}
pd.DataFrame([severity_mapping]).to_csv(SEVERITY_MAPPING_PATH,index=False)
nrisks = list(risks_description.values())
final_labeled_data[nrisks] = final_labeled_data[nrisks].replace(severity_mapping)
Add 0 to risks belonging to C40, assuming that those have reported no risks because indeed they were none, not because they were unknown¶
In [226]:
final_labeled_data.loc[final_labeled_data['c40'], nrisks] = final_labeled_data.loc[final_labeled_data['c40'], nrisks].fillna(0)
In [227]:
final_labeled_data.to_csv(LABELED_CITIES_PATH,index=False)
risks_df.to_csv(RISKS_MAPPING_PATH,index=False)
In [228]:
final_labeled_data.head()
Out[228]:
See a report of the filled labels¶
In [229]:
cw_lab_freqs = cw_data[cols_to_encode].groupby(cols_to_encode,as_index=False).size()
imp_cw_lab_freqs = cw_data_imputed[cols_to_encode].groupby(cols_to_encode,as_index=False).size()
imp_cw_ret = pd.merge(cw_lab_freqs, imp_cw_lab_freqs,suffixes=('','_imp'), how='right',on=cols_to_encode)
imp_cw_ret['increase'] = (imp_cw_ret['size_imp'] - imp_cw_ret['size'])/imp_cw_ret['size']
imp_cw_ret
Out[229]:
In [230]:
imp_report = pd.merge(imp_cw_ret, imp_ret, suffixes=('','_c40'), on=('magnitude','risks_to_citys_water_supply'))
imp_report.to_csv(IMPUTATION_REPORT_PATH,index=False)
imp_report
Out[230]:
In [ ]: